const express = require('express');
const router = express.Router();
const pool = require('../index.js'); // 引入数据库连接
router.post('/devices', (req, res) => {
  const { name, type, address, state, switch: deviceSwitch } = req.body;
  pool.query('INSERT INTO sbglb (name, type, address, state, switch) VALUES (?, ?, ?, ?, ?)', [name, type, address, state, state === '未接入' ? '无' : deviceSwitch], (err, result) => {
    if (err) {
      if (err.code === 'ER_DUP_ENTRY') {
        res.status(409).json({ message: '添加失败，设备名称已存在。' });
        return;
      }
      res.status(500).json({ message: 'Database error', error: err.message });
      return;
    }
    getDeviceCountIdByDeviceName(type, (err, countId) => {
      if (err) {
        res.status(500).json({ message: 'Database error', error: err.message });
        return;
      }
      if (countId) {
        pool.query('UPDATE device_counts SET count = count + 1 WHERE id = ?', [countId], (err, results) => {
          if (err) {
            res.status(500).json({ message: 'Database error', error: err.message });
            return;
          }
        });
      }
    res.status(201).json({ id: result.insertId, name, type, address, state, switch: deviceSwitch });
  });
});
});
router.get('/devices', (req, res) => {
  pool.query('SELECT * FROM sbglb', (err, results) => {
    if (err) {
      res.status(500).json({ message: 'Database error', error: err.message });
      return;
    }
    res.json(results);
  });
});
// 更新设备的路由
router.put('/devices/:id', (req, res) => {
  const { id } = req.params;
  const { name, type, address, state } = req.body;
  pool.query('UPDATE sbglb SET name = ?, type = ?, address = ?, state = ? WHERE id = ?', [name, type, address, state, id], (err, results) => {
    if (err) {
      res.status(500).json({ message: 'Database error', error: err.message });
      return;
    }
    if (results.affectedRows === 0) {
      res.status(404).json({ message: 'Device not found' });
      return;
    }
    // 假设您的 API 在更新后返回整个设备对象
    pool.query('SELECT * FROM sbglb WHERE id = ?', [id], (err, updatedDevice) => {
      if (err) {
        res.status(500).json({ message: 'Database error', error: err.message });
        return;
      }
      res.json({ message: 'Switch state updated successfully', device: updatedDevice[0] });
    });
  });
});
//更新开关状态路由
router.put('/devices/:id/switch', (req, res) => {
  const { id } = req.params;                // 获取设备ID
  const { switch: newSwitchState } = req.body; // 从请求体中获取新的开关状态

  // 构造新的开关状态值，如果当前是 '开'，则设置为 '关'，反之亦然
  const switchValue = newSwitchState === '开' ? 0 : 1; // 假设数据库中开关使用0和1表示

  pool.query('UPDATE sbglb SET switch = ? WHERE id = ?', [switchValue, id], (err, results) => {
    if (err) {
      res.status(500).json({ message: 'Database error', error: err.message });
      return;
    }
    if (results.affectedRows === 0) {
      res.status(404).json({ message: 'Device not found' });
      return;
    }
    res.json({ message: 'Switch state updated successfully' });
  });
});
// 删除设备的路由
router.delete('/devices/:id', (req, res) => {
  const id = req.params.id;
  
  pool.query('SELECT type, state FROM sbglb WHERE id = ?', [id], (err, typeResult) => {
    if (err) {
      return res.status(500).json({ message: 'Database error', error: err.message });
    }
    if (typeResult.length === 0) {
      return res.status(404).json({ message: 'Device not found' });
    }
    const { type, state } = typeResult[0];

    // 先删除设备
    pool.query('DELETE FROM sbglb WHERE id = ?', [id], (err, deleteResults) => {
      if (err) {
        return res.status(500).json({ message: 'Database error', error: err.message });
      }
      if (deleteResults.affectedRows === 0) {
        return res.status(404).json({ message: 'Device not found' });
      }

      // 根据设备状态更新统计数据
      const updateStats = (err, countId) => {
        if (err) {
          return res.status(500).json({ message: 'Database error', error: err.message });
        }
        let updateQuery;
        let updateValue;
        if (state === '已接入') {
          updateQuery = 'total_devices';
          updateValue = -1;
        } else {
          updateQuery = 'total_undevices';
          updateValue = -1;
        }
        pool.query('UPDATE stats SET num = num + ? WHERE stat_name = ?', [updateValue, updateQuery], (err, results) => {
          if (err) {
            return res.status(500).json({ message: 'Database error', error: err.message });
          }
          if (results.affectedRows === 0) {
            // 如果没有行被影响，插入新记录
            pool.query('INSERT INTO stats (stat_name, num) VALUES (?, ?)', [updateQuery, updateValue], (err, results) => {
              if (err) {
                return res.status(500).json({ message: 'Database error', error: err.message });
              }
            });
          }
          // 检查是否需要更新 device_counts
          getDeviceCountIdByDeviceName(type, (err, countId) => {
            if (err) {
              return res.status(500).json({ message: 'Database error', error: err.message });
            }
            if (countId) {
              pool.query('UPDATE device_counts SET count = count - 1 WHERE id = ?', [countId], (err, results) => {
                if (err) {
                  return res.status(500).json({ message: 'Database error', error: err.message });
                }
                if (results.affectedRows === 0) {
                  // 如果 count 变为 0，可能需要删除 device_counts 记录
                  pool.query('DELETE FROM device_counts WHERE id = ?', [countId], (err, results) => {
                    if (err) {
                      return res.status(500).json({ message: 'Database error', error: err.message });
                    }
                  });
                }
              });
            }
            res.json({ message: 'Device deleted successfully' });
          });
        });
      };

      // 检查 device_counts 记录是否存在
      getDeviceCountIdByDeviceName(type, updateStats);
    });
  });
});
// 获取所有设备数据
router.get('/stats', (req, res) => {
  pool.query('SELECT * FROM stats', (err, results) => {
    if (err) {
      res.status(500).json({ message: 'Database error', error: err.message });
      return;
    }
    // 将结果转换为对象，以 stat_name 为键
    const statsObject = results.reduce((acc, curr) => {
      acc[curr.stat_name] = curr.num;
      return acc;
    }, {});
    res.json(statsObject);
  });
});

// 更新设备数量
router.put('/stats/:statName', (req, res) => {
  const { num } = req.body;
  const statName = req.params.statName;
  pool.query('UPDATE stats SET num = ? WHERE stat_name = ?', [num, statName], (err, results) => {
    if (err) {
      res.status(500).json({ message: 'Database error', error: err });
      return;
    }
    if (results.affectedRows === 0) {
      // 如果没有行被影响，插入新记录
      pool.query('INSERT INTO stats (stat_name, num) VALUES (?, ?)', [statName, num], (err, results) => {
        if (err) {
          res.status(500).json({ message: 'Database error', error: err });
          return;
        }
        res.status(201).json({ id: results.insertId, stat_name: statName, num });
      });
    } else {
      res.json({ message: 'Stats updated successfully' });
    }
  });
});



function getDeviceCountIdByDeviceName(deviceName, callback) {
  pool.query('SELECT id FROM device_counts WHERE device_name = ?', [deviceName], (err, results) => {
    if (err) {
      callback(err, null);
      return;
    }
    // 确保当没有结果时，callback 能够接收到 null 而不是 undefined
    callback(null, results.length > 0 ? results[0].id : null);
  });
}
module.exports = router;